<?php
namespace Common\Model;
use Common\Model\CommonModel;
use User\Model\Orders;

class UsersModel extends CommonModel
{
	
	protected $_validate = array(
		//array(验证字段,验证规则,错误提示,验证条件,附加规则,验证时间)
		array('user_login', 'require', '用户名称不能为空！', 1, 'regex', CommonModel:: MODEL_INSERT  ),
		array('user_pass', 'require', '密码不能为空！', 1, 'regex', CommonModel:: MODEL_INSERT ),
		array('user_login', 'require', '用户名称不能为空！', 0, 'regex', CommonModel:: MODEL_UPDATE  ),
		array('user_pass', 'require', '密码不能为空！', 0, 'regex', CommonModel:: MODEL_UPDATE  ),
		array('user_login','','用户名已经存在！',0,'unique',CommonModel:: MODEL_BOTH ), // 验证user_login字段是否唯一
	    array('mobile','','手机号已经存在！',0,'unique',CommonModel:: MODEL_BOTH ), // 验证mobile字段是否唯一
	    array('mobile','/^1[34578]\d{9}$/','手机号不合法！',0,'regex',CommonModel:: MODEL_BOTH ), // 验证mobile字段是否是合法的手机号
		array('user_email','require','邮箱不能为空！',0,'regex',CommonModel:: MODEL_BOTH ), // 验证user_email字段是否唯一
		array('user_email','','邮箱帐号已经存在！',0,'unique',CommonModel:: MODEL_BOTH ), // 验证user_email字段是否唯一
		array('user_email','email','邮箱格式不正确！',0,'',CommonModel:: MODEL_BOTH ), // 验证user_email字段格式是否正确
	);
	
	protected $_auto = array(
	    array('create_time','mGetDate',CommonModel:: MODEL_INSERT,'callback'),
	    array('birthday','',CommonModel::MODEL_UPDATE,'ignore')
	);
	
	//用于获取时间，格式为2012-02-03 12:12:12,注意,方法不能为private
	function mGetDate() {
		return date('Y-m-d H:i:s');
	}
	
	protected function _before_write(&$data) {
		parent::_before_write($data);
		
		if(!empty($data['user_pass']) && strlen($data['user_pass'])<25){
			$data['user_pass']=sp_password($data['user_pass']);
		}
	}
    function retrieval($select='',$status='1,2,3,4,8',$is_ban=false,$times='loan_time'){
        if(empty($select)){
            return;
        }
        $where=array(
            'status in ('.$status.')',
        );
        $request=I('request.');
        if(!empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime($request['starts'])).'"';
        }
        if(!empty($request['ends'])){
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59',strtotime($request['ends'])).'"';
        }
        if(empty($request['ends']) && empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime('-6days')).'"';
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59').'"';
        }
        if($is_ban){
            $where[]='is_ban=1';
        }
        $groupys=array('thedate_time','loan_user');
        $request['timer_type']=empty($request['timer_type'])?0:$request['timer_type'];
        switch ($request['timer_type']){
            case '2'://周
                $select['0'] = " CONCAT(DATE_SUB({$times},INTERVAL WEEKDAY({$times})+1 DAY),' ',DATE_SUB({$times},INTERVAL WEEKDAY({$times})-5 DAY)) as thedate_time ";
                break;
            case '3'://月
                $select['0']=" CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times})) as thedate_time ";
                break;
            default://日
                break;
        }
        $groupys=' GROUP BY '.implode(',',$groupys);
        $sql='select '.implode(',',$select).' from orders where '.implode(' and ',$where).$groupys;
        $data=M("ders",'or',C('DB_CONFIG2'))->query($sql);
        return $data;
    }
    function retrievals($strs=''){
        $data=array();
        switch ($strs){
            case '1':
                $select=array(
                    'loan_time as thedate_time',
                    'sum(amount) as amount',
                    'count(id) as number',
                    '(sum(amount)-sum(interest)-sum(examine)-sum(consulting)) as real_amount',
                    '(sum(interest)+sum(examine)+sum(consulting)) as service_amount',
                    'loan_user'
                );
                $data=$this->retrieval($select);
                break;
            case '2':
                $select=array(
                    'loan_time as thedate_time',
                    'sum(amount) as huai_amount',
                    'loan_user'
                );
                $data=$this->retrieval($select,3,true);
                break;
            case '3':
                $select=array(
                    'loan_time as thedate_time',
                    'count(id) as num',
                    'loan_user'
                );
                $data=$this->retrieval($select,2,false);
                break;
        }
        return $data;
    }
    /*
     * 子订单统计
     */
    function retrieval_sub($times='a.crtime',$status='1,3,4'){
        $where=array(
            'a.status in ('.$status.')',
        );
        $request=I('request.');
        if(!empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime($request['starts'])).'"';
        }
        if(!empty($request['ends'])){
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59',strtotime($request['ends'])).'"';
        }
        if(empty($request['ends']) && empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime('-6days')).'"';
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59').'"';
        }
        switch ($status){
            case '1,3,4'://待收
                $select=array(
                    $times.' as thedate_time',
                    'sum(a.amount) as wait_amount',
                );
                break;
            case '1'://未到期
                $select=array(
                    $times.' as thedate_time',
                    'sum(a.amount) as nodue_amount',
                );
                break;
            case '4'://
                $select=array(
                    $times.' as thedate_time',
                    'sum(a.amount) as loan_amount',
                );
                break;
            case '3'://逾期
                $select=array(
                    $times.' as thedate_time',
                    'sum(a.amount) as due_amount',
                );
                break;
            case '2'://逾期
                $select=array(
                    $times.' as thedate_time',
                    'sum(a.amount) as collect_amount',
                );
                break;
            default:
                return;
        }
        $select[]='b.loan_user';
        $groupys=array('thedate_time','b.loan_user');
        $request['timer_type']=empty($request['timer_type'])?0:$request['timer_type'];
        switch ($request['timer_type']){
            case '2'://周
                $select['0'] = " CONCAT(DATE_SUB({$times},INTERVAL WEEKDAY({$times})+1 DAY),' ',DATE_SUB({$times},INTERVAL WEEKDAY({$times})-5 DAY)) as thedate_time ";
                break;
            case '3'://月
                $select['0']=" CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times})) as thedate_time ";
                break;
            default://日
                $select['0']=" DATE_FORMAT({$times},'%Y-%m-%d') as thedate_time ";
                break;
        }
        $groupys=' GROUP BY '.implode(',',$groupys);
        $sql='select '.implode(',',$select).' from orders_sub a LEFT JOIN orders b on a.pid=b.id where '.implode(' and ',$where).$groupys;
        $data=M("sub",'orders_',C('DB_CONFIG2'))->query($sql);
        return $data;
    }
    function _array_val($dt1=array(),$dt2=array(),$st='thedate_time',$type=false){
//        $request=I('request.');
//        if($request['timer_type']!=2 || $request['timer_type']!=3){
//            $request['timer_type']=1;
//        }
//        $data=array();
//        if($request['timer_type']==1){
//            if(!empty($request['starts'])){
//                $request['starts']=date('Y-m-d',strtotime($request['starts']));
//            }
//            if(!empty($request['ends'])){
//                $request['ends']=date('Y-m-d',strtotime($request['ends']));
//            }
//            if(empty($request['ends']) && empty($request['starts'])){
//                $request['starts']=date('Y-m-d',strtotime('-6days'));
//                $request['ends']=date('Y-m-d');
//            }
//            //$dt1=$this->_arr($dt1,$request['starts'],$request['ends']);
//        }
        $sting=explode(',',$st);
        if(!is_array($sting)){
            $sting[]=$st;
        }
        $num=count($sting);
        if($type && count($sting)>1){
            $d=[];
            $i=0;
            foreach($dt1 as $v){
//                $d[]=$v;
//                ++$i;
//                if($i==2){
//                    $d[]=['thedate_time'=>$v['thedate_time'],'examine_id'=>0,];
//                    //array_push();
//                    $i=0;
//                }
                $d[$v['thedate_time']]=['thedate_time'=>$v['thedate_time'],'examine_id'=>0,];
            }
            //$dt1=$d;
            $d=array_values($d);
            $dt1=array_merge($dt1,$d);
        }
        foreach($dt1 as $k=>$v){
            //$st=array();
            foreach($dt2 as $key=>$val){
                $stas=true;
                for($i=0;$i<$num;$i++){
                    if($v[$sting[$i]]!=$val[$sting[$i]]){
                        $stas=false;
                        break;
                    }
                }
                if($stas){      
                    $dt1[$k]=array_merge($v,$val);
                    unset($dt2[$key]);
                }
            }
//            $data[]=$st;
        }
        if(!empty($dt2)){
            $dt1=array_merge($dt1,$dt2);
        }
        return $dt1;
    }
    function _arr($data,$start,$end){
        $dt=array();
        $day=(strtotime($end)-strtotime($start))/(24*60*60);
        $ts=strtotime($start);
        for($i=0;$i<$day;$i++){
            if($i>0){
                $t=date('Y-m-d',strtotime('+'.$i.'days',$ts));
            }else{
                $t=$start;
            }
            $dt[]=array('thedate_time'=>$t);
        }
        foreach($dt as $k=>$v){
            foreach($data as $key=>$val){
                if($v['thedate_time']==$val['thedate_time']){
                    $dt[$k]=$val;
                    unset($data[$key]);
                }
            }
        }
        return $dt;
    }
    /*
     * 逾期金额 续期金额
     */
    function retrieval_overdue($ty=5,$times='b.loan_time'){
        $where=array(
            'a.types='.$ty,
            'a.amount>0',
        );
        $request=I('request.');
        if(!empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime($request['starts'])).'"';
        }
        if(!empty($request['ends'])){
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59',strtotime($request['ends'])).'"';
        }
        if(empty($request['ends']) && empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime('-6days')).'"';
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59').'"';
        }
        $select=array(
            $times.' as thedate_time',
            $ty==5?'sum(a.amount) as overdue_amount':'sum(a.amount) as xu_amount',
            'b.loan_user'
        );
        $groupys=array('thedate_time','b.loan_user');
        $request['timer_type']=empty($request['timer_type'])?0:$request['timer_type'];
        switch ($request['timer_type']){
            case '2'://周
                $select['0'] = " CONCAT(DATE_SUB({$times},INTERVAL WEEKDAY({$times})+1 DAY),' ',DATE_SUB({$times},INTERVAL WEEKDAY({$times})-5 DAY)) as thedate_time ";
                break;
            case '3'://月
                $select['0']=" CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times})) as thedate_time ";
                break;
            default://日
                break;
        }
        $groupys=' GROUP BY '.implode(',',$groupys);
        $sql='select '.implode(',',$select).' from system_log a left join orders b ON b.id=a.user_id where '.implode(' and ',$where).$groupys;
        $data=M("log",'system_',C('DB_CONFIG2'))->query($sql);
        return $data;
            //print_r($sql);exit;
    }
    /*
     * 子订单统计 带还逾期金额
     */
    function retrieval_sub_overdue($times='loan_time'){
        $where=array(
            'status = 3',
        );
        $request=I('request.');
        if(!empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime($request['starts'])).'"';
        }
        if(!empty($request['ends'])){
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59',strtotime($request['ends'])).'"';
        }
        if(empty($request['ends']) && empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime('-6days')).'"';
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59').'"';
        }
        $times='a.thedate_time';
        $select=array(
            $times.' as thedate_time',
            'sum(a.num)*150 as num_amount',
            'a.loan_user'
        );
        $selects=array(
            $times,
            'sum(a.num_amount) as num_amount',
            'a.loan_user'
        );
        $groupys=array('thedate_time','a.loan_user');
        $request['timer_type']=empty($request['timer_type'])?0:$request['timer_type'];
        switch ($request['timer_type']){
            case '2'://周
                $select['0'] = " CONCAT(DATE_SUB({$times},INTERVAL WEEKDAY({$times})+1 DAY),' ',DATE_SUB({$times},INTERVAL WEEKDAY({$times})-5 DAY)) as thedate_time ";
                break;
            case '3'://月
                $select['0']=" CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times})) as thedate_time ";
                break;
            default://日
                break;
        }
        $days=date('Y-m-d');
        $groupys=' GROUP BY '.implode(',',$groupys);
        $sql='select '.implode(',',$selects).' from (select '.implode(',',$select).' from (SELECT DATE_FORMAT(a.crtime, "%Y-%m-%d") AS thedate_time,TIMESTAMPDIFF(DAY,a.endtime,"'.$days.'") as num,b.loan_user FROM orders_sub a,(SELECT id,loan_user FROM orders where '.implode(' and ',$where).')b WHERE a.pid=b.id GROUP BY a.pid,b.loan_user ORDER BY a.id asc) a '.$groupys.') a '.$groupys;
        $data=M("sub",'orders_',C('DB_CONFIG2'))->query($sql);
        //print_r($sql);exit;
        return $data;
    }
    function member($status=0,$times='crtime'){
        $where=array();
        $request=I('request.');
        if(!empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime($request['starts'])).'"';
        }
        if(!empty($request['ends'])){
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59',strtotime($request['ends'])).'"';
        }
        if(empty($request['ends']) && empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime('-6days')).'"';
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59').'"';
        }
        $groupys=array('thedate_time');
        switch ($status){
            case '0':
                $select=array(
                    $times.' as thedate_time',
                    'count(id) as num',
                    'type'
                );
                $groupys[]='type';
                break;
            case '1':
                $select=array(
                    $times.' as thedate_time',
                    'count(id) as num',
                    'type_borr'
                );
                $groupys[]='type_borr';
                $where[]='type=5';
                break;
            case '2':
                break;
            default:
                return;
        }
        $request['timer_type']=empty($request['timer_type'])?0:$request['timer_type'];
        switch ($request['timer_type']){
            case '2'://周
                $select['0'] = " CONCAT(DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))+1 DAY),' ',DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))-5 DAY)) as thedate_time ";
                break;
            case '3'://月
                $select['0']=" CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times})) as thedate_time ";
                break;
            default://日
                $select['0']=" DATE_FORMAT({$times},'%Y-%m-%d') as thedate_time ";
                break;
        }
        $groupys=' GROUP BY '.implode(',',$groupys);
        $sql='select '.implode(',',$select).' from system_user where '.implode(' and ',$where).$groupys.' ORDER BY thedate_time asc';
        $data=M("user",'system_',C('DB_CONFIG2'))->query($sql);
        return $data;
    }

    function finance($status=0,$times='a.crtime'){
        $where=array(
            'a.status=1',
            //'a.examine_id>1',
        );
        $request=I('request.');
        if(!empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime($request['starts'])).'"';
        }
        if(!empty($request['ends'])){
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59',strtotime($request['ends'])).'"';
        }
        if(empty($request['ends']) && empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00').'"';
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59').'"';
        }
        $groupys=array('thedate_time');
        switch ($status){
            case '0':
                $select=array(
                    $times.' as thedate_time',
                    'SUM(a.amount) as fang_amount',
                    'a.examine_id',
                    'count(a.id) as chu_num',
                );
                $groupys[]='a.examine_id';
                $where[]  ='b.renew=0';
                $where[]  ='a.message="确认放款成功"';
                $where[]  ='a.types=1';
                break;
            case '1':
                $select=array(
                    $times.' as thedate_time',
                    'SUM(a.amount) as xu_fang_amount',
                    'a.examine_id',
                    'count(a.id) as xu_num',
                );
                $groupys[]='examine_id';
                $where[]  ='b.renew=1';
                $where[]  ='a.message="确认放款成功"';
                $where[]  ='a.types=1';
                break;
            case '2':
                $select=array(
                    $times.' as thedate_time',
                    'SUM(a.amount) as xuqi_amount',
                    'a.examine_id',
                    'sum(a.wechat_amount) as wechat_amount2',//续期的微信实收
                    'sum(a.alipay_amount) as alipay_amount2',//续期的支付宝实收
                    'sum(a.bank_amount) as bank_amount2',//续期的支付宝实收
                    'sum(a.real_amount) as real_amount2',
                    'sum(a.overdue_amount) as overdue_amount2',
                );
                $groupys[]='examine_id';
                $where[]  ='a.types=3';
                break;
            case '3':
                $select=array(
                    $times.' as thedate_time',
                    'SUM(a.amount) as yuqi_amount',
                    'a.examine_id',
                    'sum(a.wechat_amount) as wechat_amount3',//逾期的微信实收
                    'sum(a.alipay_amount) as alipay_amount3',//逾期的支付宝实收
                    'sum(a.bank_amount) as bank_amount3',//逾期的支付宝实收
                    'sum(a.real_amount) as real_amount3',
                    'sum(a.overdue_amount) as overdue_amount3',
                );
                $groupys[]='examine_id';
                $where[]  ='a.types=5';
                break;
            case '4':
                $select=array(
                    $times.' as thedate_time',
                    'a.examine_id',
                    'sum(a.amount) as order_amount',
//                    'sum(a.wechat_amount) as wechat_amount4',//后台人工审核的微信实收
//                    'sum(a.alipay_amount) as alipay_amount4',//后台人工审核的支付宝实收
//                    'sum(a.bank_amount) as bank_amount4'//后台人工审核的银行实收
               	);
               	$groupys[]='examine_id';
               	$where[]  ='a.types=1';
               	$where[]  ='a.message like "人工审核还款%"';
               	break;
            case '5':
                $select=array(
                    $times.' as thedate_time',
                    'a.examine_id',
                    'sum(a.amount) as y_amount',
                    'sum(a.wechat_amount) as wechat_amount5',//后台人工审核的微信实收
                    'sum(a.alipay_amount) as alipay_amount5',//后台人工审核的支付宝实收
                    'sum(a.bank_amount) as bank_amount5',//后台人工审核的银行实收
                    'sum(a.real_amount) as real_amount5',
                    'sum(a.overdue_amount) as overdue_amount5',
                );
                $groupys[]='examine_id';
                $where[]  ='a.types=4';
                break;
            case '6':
                $select=array(
                    $times.' as thedate_time',
                    'a.examine_id',
                    'sum(a.amount) as y_amount',
                );
                $groupys[]='examine_id';
                $where[]  ='a.types=7';
                break;
            case '7':
                $select=array(
                    $times.' as thedate_time',
                    'a.examine_id',
                    'sum(a.wechat_amount) as wechat_amount4',//后台人工审核的微信实收
                    'sum(a.alipay_amount) as alipay_amount4',//后台人工审核的支付宝实收
                    'sum(a.bank_amount) as bank_amount4',//后台人工审核的银行实收
                    'sum(a.real_amount) as real_amount4',
                    'sum(a.overdue_amount) as overdue_amount4',
                );
                $groupys[]='examine_id';
                $where[]  ='a.types=1';
                $where[]  ='a.message="三个实收"';
                break;
            default:
                return;
        }
        $request['timer_type']=empty($request['timer_type'])?0:$request['timer_type'];
        switch ($request['timer_type']){
            case '2'://周
                $select['0'] = " CONCAT(DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))+1 DAY),' ',DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))-5 DAY)) as thedate_time ";
                break;
            case '3'://月
                $select['0']=" CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times})) as thedate_time ";
                break;
            default://日
                $select['0']=" DATE_FORMAT({$times},'%Y-%m-%d') as thedate_time ";
                break;
        }
        $groupys=' GROUP BY '.implode(',',$groupys);
        $sql='select '.implode(',',$select).' from system_log a LEFT JOIN orders b ON a.user_id=b.id  where '.implode(' and ',$where).$groupys.' ORDER BY thedate_time asc';
//        if($status==4){
//            print_r($sql);exit;
//        }
        $data=M("log",'system_',C('DB_CONFIG2'))->query($sql);
        return $data;
    }
    /*
     * 财务
     */
    function finance_service($status=0,$times='a.crtime'){
        $where=array(
            'a.message="确认放款成功"',
            'a.status=1',
            'a.types=1',
            //'a.examine_id>1',
        );
        $request=I('request.');
        if(!empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime($request['starts'])).'"';
        }
        if(!empty($request['ends'])){
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59',strtotime($request['ends'])).'"';
        }
        if(empty($request['ends']) && empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00').'"';
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59').'"';
        }
        $groupys=array('thedate_time');
        switch ($status){
            case '0':
                $select=array(
                    $times.' as thedate_time',
                    'SUM(b.interest) as interest',
                    'SUM(b.examine) as examine',
                    'SUM(b.consulting) as consulting',
                    'SUM(b.amount) as total_amount',
                    'a.examine_id'
                );
                $where[]  ='b.renew=0';
                break;
            case '1':
                $select=array(
                    $times.' as thedate_time',
                    'SUM(b.interest) as xu_interest',
                    'SUM(b.examine) as xu_examine',
                    'SUM(b.consulting) as xu_consulting',
                    'SUM(b.amount) as xu_total_amount',
                    'a.examine_id'
                );
                $where[]  ='b.renew=1';
                break;
            default:
                return;
        }
        $groupys[]='a.examine_id';
        $request['timer_type']=empty($request['timer_type'])?0:$request['timer_type'];
        switch ($request['timer_type']){
            case '2'://周
                $select['0']=" CONCAT(DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))+1 DAY),' ',DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))-5 DAY)) as thedate_time ";
                break;
            case '3'://月
                $select['0']=" CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times})) as thedate_time ";
                break;
            default://日
                $select['0']=" DATE_FORMAT({$times},'%Y-%m-%d') as thedate_time ";
                break;
        }
        $groupys=' GROUP BY '.implode(',',$groupys);
        $sql='select '.implode(',',$select).' from system_log a LEFT JOIN orders b ON a.user_id=b.id  where '.implode(' and ',$where).$groupys.' ORDER BY thedate_time asc';
       //print_r($sql);
        $data=M("log",'system_',C('DB_CONFIG2'))->query($sql);
        return $data;
    }

    private $_ordersModel = null;

    /**
     * @return object   [返回订单模型对象]
     */
    public function getOrdersModel()
    {
        if(!$this->_ordersModel instanceof Orders){
            $this->_ordersModel = new Orders();
        }
        return $this->_ordersModel;
    }

    /**
     * 该方法作废
     * @use          [已收的服务费]
     * @author       chenxiaogang
     * @param int   $is_xu [统计：0初次，1续借，2统计全部已收]
     * @param string $times
     */
    public function finance_service_income($is_xu,$times='l.crtime'){
        $where=[
            "(l.message LIKE '人工审核还款%' OR l.message='人工还款')",
            'l.`status`=1',
            //'l.types>1',
            //'l.examine_id>0',//有可能是系统自动收的、客户自己还款，所以这里注释掉
        ];
        $is_xu = (int)$is_xu;
        if($is_xu === 0 || $is_xu === 1){//判断要初次已收还是续期已收，还是全部已收
            $where[] = 'o.renew='.$is_xu;
        }
        $request=I('request.');
        if(!empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime($request['starts'])).'"';
        }
        if(!empty($request['ends'])){
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59',strtotime($request['ends'])).'"';
        }
        if(empty($request['ends']) && empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00').'"';
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59').'"';
        }
        switch(intval($is_xu)){
            case 0:
                $alias = 'chu_paid_services';
                break;
            case 1:
                $alias = 'xu_paid_services';
                break;
            case 2:;
                $alias = 'all_paid_services';
                break;
        }
        $fields = [
            'l.examine_id',
            $times.' as thedate_time',
            'TRUNCATE(SUM((o.`interest`+o.`examine`+o.`consulting`)/o.stages),2) AS '.$alias,
        ];
        $request['timer_type']=empty($request['timer_type'])?0:$request['timer_type'];
        switch ($request['timer_type']){
            case '2'://周
                $fields["CONCAT(DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))+1 DAY),' ',DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))-5 DAY))"] = 'thedate_time';
                break;
            case '3'://月
                $fields["CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times}))"] = 'thedate_time';
                break;
            default://日
                $fields["DATE_FORMAT({$times},'%Y-%m-%d')"] = 'thedate_time';
                break;
        }
        $where = implode(' and ',$where);
        $data = M('log','system_',C('DB_CONFIG2'))->alias('l')
            ->field($fields)
            ->join('orders o ON l.user_id=o.id','left')
            ->where($where)
            ->group('thedate_time,l.examine_id')
            ->order('thedate_time asc')
            ->select();
        //$t = M('log','system_',C('DB_CONFIG2'))->_sql();
        return $data;
    }

    /**
     * 该方法作废
     * @use          [总服务费（按期数算服务费）]
     * @author       chenxiaogang
     */
    public function finance_service_paid_total($times='crtime'){
        $where=array(
            'status=1',
            'types=0',
            'examine_id>1',
        );
        $request=I('request.');
        if(!empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime($request['starts'])).'"';
        }
        if(!empty($request['ends'])){
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59',strtotime($request['ends'])).'"';
        }
        if(empty($request['ends']) && empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00').'"';
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59').'"';
        }
        $fields = [
            'examine_id','crtime'=>'thedate_time',
            "SUM(IF(RIGHT(message,3)='咨询费',amount,0))"=>'zixun_total',
            "SUM(IF(RIGHT(message,3)='审核费',amount,0))"=>'shenhe_total',
            "SUM(IF(RIGHT(message,3)='顾问费',amount,0))"=>'guwen_total',
        ];
        $request['timer_type']=empty($request['timer_type'])?0:$request['timer_type'];
        switch ($request['timer_type']){
            case '2'://周
                $fields["CONCAT(DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))+1 DAY),' ',DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))-5 DAY))"] = 'thedate_time';
                break;
            case '3'://月
                $fields["CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times}))"] = 'thedate_time';
                break;
            default://日
                $fields["DATE_FORMAT({$times},'%Y-%m-%d')"] = 'thedate_time';
                break;
        }
        $where = implode(' and ',$where);
        $data = M("log",'system_',C('DB_CONFIG2'))->field($fields)->where($where)->group('thedate_time,examine_id')->order('thedate_time asc')->select();
        return $data;
    }

    public function finance_service_income2($is_xu,$times='a.crtime')
    {
        $request=I('request.');
        $where = $this->getDateWhere();
        $where[] = 'o.renew='.$is_xu;
        $where = implode(' and ',$where);
        $select=[
            $times.' as thedate_time',
            'a.examine_id',
            'SUM(o.`interest`+o.`examine`+o.`consulting`) as '.($is_xu?'xu_paid_services':'chu_paid_services'),
        ];
        $request['timer_type']=empty($request['timer_type'])?0:$request['timer_type'];
        switch ($request['timer_type']){
            case '2'://周
                $select['0']=" CONCAT(DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))+1 DAY),' ',DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))-5 DAY)) as thedate_time ";
                break;
            case '3'://月
                $select['0']=" CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times})) as thedate_time ";
                break;
            default://日
                $select['0']=" DATE_FORMAT({$times},'%Y-%m-%d') as thedate_time ";
                break;
        }
        $sql = 'SELECT 
                '.implode(',',$select).'
                FROM orders o,
                (
                SELECT l.user_id,l.`crtime`,l.`examine_id` FROM system_log l
                RIGHT JOIN orders o ON o.id=l.user_id
                WHERE 
                (l.message LIKE "人工审核还款%" OR l.`message`="人工还款" OR l.message="借款还款")
                AND l.`status`=1 
                AND l.`types`>0 
                AND o.status=2
                AND '.$where.'
                GROUP BY l.user_id
                ORDER BY l.id desc
                ) a 
                WHERE o.id=a.user_id
                 group by thedate_time,a.examine_id';
        //print_r($sql);exit;
        $data = $this->getOrdersModel()->query($sql);
        return $data;
    }

    //该方法作废
    public function finance_service_income3($is_xu,$times='l.crtime')
    {
        $where = $this->getDateWhere($times);
        $where[] = 'o.renew='.$is_xu;
        $where = implode(' and ',$where);
        $select=[
            'a.thedate_time',
            'a.examine_id',
            '(o.`interest`+o.`examine`+o.`consulting`) as '.($is_xu?'xu_paid_services':'chu_paid_services'),
        ];
        $request['timer_type']=empty($request['timer_type'])?0:$request['timer_type'];
        $thedate_time = '';
        switch ($request['timer_type']){
            case '2'://周
                $thedate_time=" CONCAT(DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))+1 DAY),' ',DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))-5 DAY)) as thedate_time ";
                break;
            case '3'://月
                $thedate_time=" CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times})) as thedate_time ";
                break;
            default://日
                $thedate_time=" DATE_FORMAT({$times},'%Y-%m-%d') as thedate_time ";
                break;
        }
        $sql = 'SELECT 
                a.thedate_time,
                a.examine_id,
                (o.`interest`+o.`examine`+o.`consulting`)
                FROM orders o ,
                (
                SELECT l.user_id,'.$thedate_time.' AS thedate_time,l.`examine_id` FROM system_log l
                LEFT JOIN orders o ON o.id=l.user_id
                WHERE 
                (l.message LIKE "人工审核还款%" OR l.`message`="人工还款" OR l.message="借款还款")
                AND l.`status`=1 
                AND l.`types`>0 
                AND o.status=2
                GROUP BY l.user_id
                ) a 
                WHERE o.id=a.user_id
                AND s.status=2 AND '.$where.' group by thedate_time,a.examine_id';
        $data = $this->getOrdersModel()->query($sql);
        return $data;
    }

    /**
     * @use          [获取查询条件]
     * @author       chenxiaogang
     * @param  string   $times
     * @return array    $where
     */
    private function getDateWhere($times='l.crtime'){
        $request=I('request.');
        $where = [];
        if(!empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00',strtotime($request['starts'])).'"';
        }
        if(!empty($request['ends'])){
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59',strtotime($request['ends'])).'"';
        }
        if(empty($request['ends']) && empty($request['starts'])){
            $where[]  =$times.' >= "'.date('Y-m-d 00:00:00').'"';
            $where[]  =$times.' <= "'.date('Y-m-d 23:59:59').'"';
        }
        return $where;
    }

    /**
     * 提现统计
     * @param int $status -1全部0待审核1不通过2提现成功
     * @param string $times
     * @return mixed
     */
    public function all_reflect($status=-1,$times='crtime'){
        if('-1'!=$status){
            $where[] = 'status = '.$status;
        }
        $request=I('request.');
        if(!empty($request['starts'])){
            $where[] = $times.' >= "'.date('Y-m-d 00:00:00',strtotime($request['starts'])).'"';
        }
        if(!empty($request['ends'])){
            $where[] = $times.' <= "'.date('Y-m-d 23:59:59',strtotime($request['ends'])).'"';
        }
        if(empty($request['ends']) && empty($request['starts'])){
            $where[] = $times.' >= "'.date('Y-m-d 00:00:00').'"';
            $where[] = $times.' <= "'.date('Y-m-d 23:59:59').'"';
        }
        $request['timer_type']=empty($request['timer_type'])?0:$request['timer_type'];
        switch ($request['timer_type']){
            case '2'://周
                $select['0'] = " CONCAT(DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))+1 DAY),' ',DATE_SUB(DATE_FORMAT({$times},'%Y-%m-%d'),INTERVAL WEEKDAY(DATE_FORMAT({$times},'%Y-%m-%d'))-5 DAY)) as thedate_time ";
                break;
            case '3'://月
                $select['0']=" CONCAT(DATE_FORMAT(LAST_DAY({$times}),'%Y-%m-'),'01',' ',LAST_DAY({$times})) as thedate_time ";
                break;
            default://日
                $select['0']=" DATE_FORMAT({$times},'%Y-%m-%d') as thedate_time ";
                break;
        }
        $sql = 'SELECT '.implode(',',$select).',SUM(amount) AS amount,
                    SUM(IF(`status`=0,amount,0)) AS amount0,
                    SUM(IF(`status`=1,amount,0)) AS amount1,
                    SUM(IF(`status`=2,amount,0)) AS amount2 FROM system_apply WHERE '
            .implode(' and ',$where).' GROUP BY thedate_time 
            ORDER BY crtime DESC,crtime ASC,id ASC';
        $data = M("apply",'system_',C('DB_CONFIG2'))->query($sql);
        return $data;
    }

}

